import json
import math
from decimal import Decimal

from django.db import connection
from pymysql.converters import escape_string

from app.model.allocate_goods import AllocateGoods
from app.model.cs_goods_update_logs import CsGoodsUpdateLogs
from app.model.table_goods import TableGoods
from app.service import goods_barcode, shop
from libs import common
from libs.common import get_ts
from libs.db import DB

GoodsDetailFields = ['id', 'class_id', 'goods_name', 'usdt_price', 'scount',
                     'is_ban', 'is_delete', 'create_time', 'state', 'shoper_id',
                     'is_community', 'point_ratio', 'is_warehouse', 'device_goods_id', 'cost_price',
                     'member_price', 'barcode', 'ver', 'unit', 'guardian_ratio',
                     'spec_typ', 'scale_no', 'is_fast_food', 'product_date', 'guarantee_period',
                     'guarantee_type', 'is_half','drink_spec','menu_desc','drink_flavorset']

GoodsListFields = ['id', 'class_id', 'goods_name', 'usdt_price', 'scount',
                   'is_ban', 'is_delete', 'create_time', 'state', 'shoper_id',
                   'is_community', 'point_ratio', 'is_warehouse', 'device_goods_id', 'cost_price',
                   'member_price', 'barcode', 'ver', 'unit', 'guardian_ratio',
                   'spec_typ', 'scale_no', 'is_fast_food', 'is_half','drink_spec','menu_desc','drink_flavorset']

ScanGoodsListFields = ['id', 'class_id', 'goods_name', 'usdt_price', 'scount',
                       'point_ratio', 'cost_price', 'member_price', 'unit', 'spec_typ',
                       'scale_no', 'is_fast_food', 'is_half','drink_spec','menu_desc','drink_flavorset']


def query_shop_goods_by_keyword(shop_id, keyword, sub_keyword, page, class_id=0, fast_food_flag=0, keyword_type=None):
    goods_arr = []
    escape_keyword = escape_string(keyword)
    if keyword == '':
        query_cond = ''
    else:
        if keyword_type == 1:
            if sub_keyword == '':
                query_cond = ' and (cgb.barcode="{}" or tg.barcode="{}")'.format(escape_keyword, escape_keyword)
            else:
                escape_sub_keyword = escape_string(sub_keyword)
                query_cond = ' and (cgb.barcode="{}"' \
                             ' or tg.barcode="{}"' \
                             ' or (tg.spec_typ=1 and tg.barcode="{}"))'.format(escape_keyword,
                                                                               escape_keyword,
                                                                               escape_sub_keyword)
        else:
            if sub_keyword == '':
                query_cond = ' and (cgb.barcode like "%%{}%%"' \
                             ' or tg.barcode like "%%{}%%"' \
                             ' or tg.goods_name like "%%{}%%")'.format(escape_keyword,
                                                                       escape_keyword,
                                                                       escape_keyword)
            else:
                escape_sub_keyword = escape_string(sub_keyword)
                query_cond = ' and (cgb.barcode like "%%{}%%"' \
                             ' or tg.barcode like "%%{}%%"' \
                             ' or tg.goods_name like "%%{}%%"' \
                             ' or (tg.spec_typ=1 and tg.barcode="{}"))'.format(escape_keyword,
                                                                               escape_keyword,
                                                                               escape_keyword,
                                                                               escape_sub_keyword)
    if class_id > 0:
        query_cond += ' and tg.class_id={}'.format(class_id)
    if fast_food_flag == 1:
        query_cond += ' and tg.is_fast_food>0'
    fields = 'DISTINCT tg.id,tg.class_id,tg.goods_name,tg.usdt_price,tg.scount,' \
             'tg.is_ban,tg.create_time,tg.state,tg.shoper_id shop_id,' \
             'tg.is_community,tg.point_ratio,tg.is_warehouse,tg.cost_price,tg.member_price,' \
             'tg.py_code,tg.barcode,tg.unit,tg.spec_typ,tg.scale_no,tg.is_fast_food,' \
             'tg.product_date,tg.guarantee_period,tg.guarantee_type,tg.is_half,tgc.class_name,tgc.risk_grade,tg.drink_spec,tg.menu_desc,tg.drink_flavorset'
    where = 'WHERE tg.shoper_id="{}" and tg.is_delete=0{}'.format(shop_id, query_cond)
    sql = 'SELECT {} FROM table_goods tg' \
          ' LEFT JOIN table_goods_class tgc ON tgc.id=tg.class_id' \
          ' LEFT JOIN cs_goods_barcodes cgb ON tg.id=cgb.goods_id AND tg.shoper_id=cgb.shop_id {}' \
          ' {}'.format(fields, where, page.get_limit())
    db_goods = TableGoods.objects.raw(sql)
    row_num = len(db_goods)
    fields = ['id', 'class_id', 'goods_name', 'usdt_price', 'scount',
              'is_ban', 'create_time', 'state', 'shop_id', 'is_community',
              'point_ratio', 'is_warehouse', 'cost_price', 'member_price', 'py_code',
              'barcode', 'unit', 'spec_typ', 'scale_no', 'is_fast_food',
              'product_date', 'guarantee_period', 'guarantee_type', 'is_half', 'class_name',
              'risk_grade','drink_spec','menu_desc','drink_flavorset']
    for n in range(0, row_num):
        obj = db_goods[n]
        goods_data = {}
        for key in fields:
            goods_data[key] = getattr(obj, key)
        goods_arr.append(goods_data)
    return goods_arr


def count_shop_goods_by_keyword(shop_id, keyword, sub_keyword, class_id=0, fast_food_flag=0, keyword_type=None):
    escape_keyword = escape_string(keyword)
    if keyword == '':
        query_cond = ''
    else:
        if keyword_type == 1:
            if sub_keyword == '':
                query_cond = ' and (cgb.barcode="{}" or tg.barcode="{}")'.format(escape_keyword, escape_keyword)
            else:
                escape_sub_keyword = escape_string(sub_keyword)
                query_cond = ' and (cgb.barcode="{}"' \
                             ' or tg.barcode="{}"' \
                             ' or (tg.spec_typ=1 and tg.barcode="{}"))'.format(escape_keyword,
                                                                               escape_keyword,
                                                                               escape_sub_keyword)
        else:
            if sub_keyword == '':
                query_cond = ' and (cgb.barcode like "%{}%"' \
                             ' or tg.barcode like "%{}%"' \
                             ' or tg.goods_name like "%{}%")'.format(escape_keyword,
                                                                     escape_keyword,
                                                                     escape_keyword)
            else:
                escape_sub_keyword = escape_string(sub_keyword)
                query_cond = ' and (cgb.barcode like "%{}%"' \
                             ' or tg.barcode like "%{}%"' \
                             ' or tg.goods_name like "%{}%"' \
                             ' or (tg.spec_typ=1 and tg.barcode="{}"))'.format(escape_keyword,
                                                                               escape_keyword,
                                                                               escape_keyword,
                                                                               escape_sub_keyword)
    if class_id > 0:
        query_cond += ' and tg.class_id={}'.format(class_id)
    if fast_food_flag == 1:
        query_cond += ' and tg.is_fast_food>0'
    where = 'WHERE tg.shoper_id="{}" and tg.is_delete=0{}'.format(shop_id, query_cond)
    sql = 'SELECT COUNT(distinct tg.id) num FROM table_goods tg' \
          ' LEFT JOIN table_goods_class tgc ON tgc.id=tg.class_id' \
          ' LEFT JOIN cs_goods_barcodes cgb ON tg.id=cgb.goods_id AND tg.shoper_id=cgb.shop_id {}'.format(where)
    num = 0
    with connection.cursor() as cursor:
        cursor.execute(sql)
        row = cursor.fetchone()
        if row is not None:
            num = row[0]
    return num


def query_goods_by_id(shop_id, goods_id, fields=None, my_filters=None):
    if fields is None:
        fields = GoodsDetailFields
    goods_data = {}
    try:
        filters = {
            'id': goods_id,
            'shoper_id': shop_id,
        }
        if type(my_filters) is dict:
            filters.update(my_filters)
        goods_data = TableGoods.objects.filter(**filters).values(*fields).get()
    except Exception as e:
        print(e.args)
    return goods_data


def query_assoc_goods(goods_id: int):
    goods_data = {}
    with connection.cursor() as cursor:
        sql = 'select id,scount,pack_qty,ver from table_goods where id=%s'
        cursor.execute(sql, goods_id)
        row = cursor.fetchone()
        if row is not None:
            goods_data = {
                'id': row[0],
                'scount': row[1],
                'pack_qty': row[2],
                'ver': row[3],
            }
    return goods_data

def query_assoc_goods1(goods_id: int):
    goods_data = {}
    with connection.cursor() as cursor:
        sql = 'select id,scount,pack_qty,ver from table_goods where assoc_goods_id=%s'
        cursor.execute(sql, goods_id)
        row = cursor.fetchone()
        if row is not None:
            goods_data = {
                'id': row[0],
                'scount': row[1],
                'pack_qty': row[2],
                'ver': row[3],
            }
    return goods_data

# 组装、拆包商品
# 给定一种商品A，查询关联商品B，返回组装或者拆分的结果。
# 根据A商品的pack_qty判断需要组装或者拆分
# pack_qty>1，表示需要通过商品B组装
# pack_qty<=1，表示需要通过商品B拆分
def allocate_goods(a_goods: AllocateGoods):
    result = {
        'err': None,
        'typ': '',
        'allocate_num': 0,  # 关联商品分配的数量
        'to_unit_num': 0,  # 一或多个整件商品拆分出的单件数量
        'assoc_goods': None,
    }
    if a_goods.need_amount == 0:
        result['err'] = '购入商品数量为0'
        return result
    if a_goods.assoc_goods_id == a_goods.id:
        result['err'] = '关联商品与商品为同一ID'
        return result
    if a_goods.need_amount > 0:
        if a_goods.assoc_goods_id > 0:
            assoc_goods_data = query_assoc_goods(a_goods.assoc_goods_id)
            assoc_goods_id = assoc_goods_data.get('id', 0)
            if assoc_goods_id == 0:
                result['err'] = '关联商品不存在'
                return result
            result['assoc_goods'] = assoc_goods_data
            assoc_goods_stock_num = assoc_goods_data.get('scount', 0)
            if assoc_goods_stock_num <= 0:
                result['err'] = '关联商品库存不足'
                return result
            if a_goods.pack_qty <= 0:
                result['err'] = '关联商品无法组装'
                return result
            # 给定商品A整包商品，需要通过商品B组装，计算组装成包，需要商品B的数量
            result['typ'] = 'pack'
            allocate_num = a_goods.pack_qty * a_goods.need_amount
            if assoc_goods_stock_num < allocate_num:
                result['err'] = '关联商品库存不足,剩余数量：{}'.format(assoc_goods_stock_num)
                return result
            result['allocate_num'] = allocate_num
            result['to_unit_num'] = a_goods.need_amount
        if a_goods.assoc_goods_id == 0:
            assoc_goods_data1 = query_assoc_goods1(a_goods.id)
            assoc_goods_id1 = assoc_goods_data1.get('id', 0)
            if assoc_goods_id1 == 0:
                result['err'] = '关联商品不存在'
                return result
            result['assoc_goods'] = assoc_goods_data1
            assoc_goods_pack_qty = assoc_goods_data1.get('pack_qty', 0)
            if assoc_goods_pack_qty <= 0:
                result['err'] = '关联商品无法拆分'
                return result
            assoc_goods_stock_num1 = assoc_goods_data1.get('scount', 0)
            need_pack_num = math.ceil(a_goods.need_amount / assoc_goods_pack_qty)
            if assoc_goods_stock_num1 <= 0:
                result['err'] = '关联商品库存不足'
                return result
            if assoc_goods_stock_num1 < need_pack_num:
                result['err'] = '关联商品库存不足,剩余数量：{}'.format(assoc_goods_stock_num1)
                return result
            # 给定商品A为单件商品，需要拆分商品B，计算拆分成单件，需要商品B的数量 
            result['typ'] = 'split'
            result['allocate_num'] = need_pack_num
            result['to_unit_num'] = need_pack_num * assoc_goods_pack_qty
    else:
        result['err'] = '关联商品不存在'
        return result
    return result


def query_goods_in_ids(goods_id_arr):
    goods_dict = {}  # 购物车内包含商品字典
    if len(goods_id_arr) == 0:
        return goods_dict
    goods_ids_str = ','.join(goods_id_arr)
    sql = 'select id,scount,sell_count,cost_price,ver,pack_qty,assoc_goods_id,is_fast_food,menu_desc,drink_flavorset' \
          ' from table_goods where id in({})'.format(goods_ids_str)
    db_goods_rows = TableGoods.objects.raw(sql)
    for db_goods_row in db_goods_rows:
        goods_dict[db_goods_row.id] = db_goods_row
    return goods_dict


def get_goods_img_by_id(goods_id):
    with connection.cursor() as cursor:
        dh = DB('table_thumbnail_imgs', conn=cursor)
        row = dh.order_by('order_id desc').one(['oss_url'], {'goods_id': goods_id})
        goods_img = row.get('oss_url', '')
    return goods_img


def get_goods_content_img_by_id(goods_id):
    with connection.cursor() as cursor:
        dh = DB('table_content_imgs', conn=cursor)
        row = dh.order_by('order_id desc').one(['oss_url'], {'goods_id': goods_id})
        goods_img = row.get('oss_url', '')
    return goods_img


def query_goods_img_by_ids(goods_id_arr, cursor):
    goods_img_map = {}
    goods_ids_str = ','.join(goods_id_arr)
    where = 'goods_id in({}) group by goods_id order by order_id desc'.format(goods_ids_str)
    dh = DB('table_thumbnail_imgs', conn=cursor)
    db_imgs = dh.find(['goods_id', 'oss_url'], where)
    for db_img in db_imgs:
        goods_img_map[db_img.get('goods_id')] = db_img.get('oss_url')
    return goods_img_map


def query_goods_content_in_ids(goods_id_arr, cursor):
    goods_img_map = {}  # 购物车内包含商品字典
    if len(goods_id_arr) == 0:
        return goods_img_map
    goods_ids_str = ','.join(goods_id_arr)
    where = 'goods_id in({})'.format(goods_ids_str)
    dh = DB('table_content_imgs', conn=cursor)
    db_imgs = dh.find(['goods_id', 'oss_url'], where)
    for db_img in db_imgs:
        goods_img_map[db_img.get('goods_id')] = db_img.get('oss_url')
    return goods_img_map


def save_goods_history_data(goods_id, goods_history_data):
    err = None
    try:
        for k in goods_history_data:
            v = goods_history_data[k]
            typ = type(goods_history_data[k])
            if typ is not str:
                try:
                    goods_history_data[k] = str(v)
                except Exception as e:
                    err = '__err__'
                    print(err, e)
                    goods_history_data[k] = err
        goods_serialized = json.dumps(goods_history_data)
        now = get_ts()
        sell_price = goods_history_data.get('usdt_price', 0)
        csGoodsUpdateLogs = CsGoodsUpdateLogs()
        csGoodsUpdateLogs.goods_id = goods_id
        csGoodsUpdateLogs.sell_price = sell_price
        csGoodsUpdateLogs.goods_his_data = goods_serialized
        csGoodsUpdateLogs.created = now
        csGoodsUpdateLogs.save()
    except Exception as e:
        err = '保存商品历史数据失败'
        print(err, e)
        print(goods_history_data)
    return err


def save_goods_image(cursor, goods_id, img):
    dh = DB('table_thumbnail_imgs', conn=cursor)
    db_img = dh.order_by('order_id desc').one(['id'], {'goods_id': goods_id, 'is_delete': 0})
    db_img_id = db_img.get('id', 0)
    if db_img_id == 0:
        db_img_id, _ = dh.insert({
            'goods_id': goods_id,
            'oss_url': img,
            'is_delete': 0,
            'order_id': 0,
        })
    else:
        dh.update({
            'oss_url': img
        }, {
            'id': db_img_id
        })
    return db_img_id


def save_goods_content_image(cursor, goods_id, img):
    dh = DB('table_content_imgs', conn=cursor)
    db_img = dh.order_by('order_id desc').one(['id'], {'goods_id': goods_id, 'is_delete': 0})
    db_img_id = db_img.get('id', 0)
    if db_img_id == 0:
        db_img_id, _ = dh.insert({
            'goods_id': goods_id,
            'oss_url': img,
            'is_delete': 0,
            'order_id': 0,
        })
    else:
        dh.update({
            'oss_url': img
        }, {
            'id': db_img_id
        })
    return db_img_id


def query_goods_by_barcode(cursor, shop_id, barcode, diy_barcode, page, class_id=0, fast_food_flag=0):
    dh = DB('cs_goods_barcodes', conn=cursor, table_as='gb')
    left_join_tables = {
        'tg': {'table': 'table_goods', 'on': 'gb.goods_id=tg.id'}
    }
    left_join_fields = {
        'gb': ['barcode'],
        'tg': ScanGoodsListFields,
    }
    gb_where = {
        'shop_id': shop_id,
        'barcode': barcode,
    }
    if diy_barcode != '':
        gb_where = {
            'shop_id': shop_id,
            'barcode in ("{}", "{}")'.format(barcode, diy_barcode): None,
        }
    tg_where = {
        'is_delete': 0,
    }
    if fast_food_flag == 1:
        tg_where.update({'is_fast_food > 0': None})#'is_fast_food': 1
    if class_id > 0:
        tg_where.update({'class_id': class_id})
    left_join_wheres = {
        'gb': gb_where,
        'tg': tg_where
    }
    total = dh.left_join(left_join_tables).count(left_join_wheres)
    rows = []
    if total > 0:
        rows = dh.left_join(left_join_tables).page(page).find(left_join_fields, left_join_wheres)
    return total, rows


def query_goods_list(shop_id, keyword, sub_keyword, class_id, fast_food_flag, keyword_type, page):
    goods_arr = []
    total = count_shop_goods_by_keyword(shop_id, keyword, sub_keyword, class_id, fast_food_flag, keyword_type)
    if total > 0:
        goods_arr = query_shop_goods_by_keyword(shop_id, keyword, sub_keyword, page, class_id, fast_food_flag,
                                                keyword_type)
        query_goods_img(goods_arr)
    return total, goods_arr


def query_goods_img(goods_arr):
    if len(goods_arr) > 0:
        goods_id_arr = []
        for goods_item in goods_arr:
            goods_id_arr.append(str(goods_item.get('id')))
            goods_item.update({'point_ratio': goods_item.get('point_ratio') * 100})
        with connection.cursor() as cursor:
            goods_img_map = query_goods_img_by_ids(goods_id_arr, cursor)
            goods_content_img_map = query_goods_content_in_ids(goods_id_arr, cursor)
        for goods_item in goods_arr:
            goods_id = goods_item.get('id')
            oss_url = goods_img_map.get(goods_id, '')
            goods_item['goods_img'] = oss_url
            oss_url = goods_content_img_map.get(goods_id, '')
            goods_item['goods_content_img'] = oss_url


def query_goods_by_keyword(form, auth, page, optimize=False):
    total = 0
    goods_arr = []
    weight_str = ''
    resp_ext = {}
    shop_is_half = 0
    try:
        keyword = form.get('keyword')
        keyword_type = form.get_int('keyword_type', default=None)
        fast_food_flag = form.get_int('is_fast_food')
        class_id = form.get_int('class_id')
        shop_id = auth.get('shop_id')
        sub_keyword = ''
        keyword_len = len(keyword)
        keyword_isnumeric = keyword.isnumeric()
        mode = 0  # mode=1时，关键字视为条形码
        if keyword_isnumeric:
            if keyword_len == 13:
                sub_keyword = keyword[2:7]
                parsed_weight_str = keyword[7:12]
                parsed_weight_dec = common.str_to_dec(parsed_weight_str)
                if parsed_weight_dec > 0:
                    parsed_weight_dec = parsed_weight_dec * Decimal('0.001')
                    weight_str = str(parsed_weight_dec)
            if optimize:
                if keyword_len >= 5:
                    mode = 1
        with connection.cursor() as cursor:
            db_shop = shop.get_shop_by_id(cursor, shop_id, ['is_half'])
            db_shop_is_half = db_shop.get('is_half', 0)
            if mode == 1:
                total, goods_arr = query_goods_by_barcode(cursor, shop_id, keyword, sub_keyword, page, class_id,
                                                          fast_food_flag)
                get_goods_class_by_goods_ids(goods_arr)
                if fast_food_flag == 1:
                    query_goods_img(goods_arr)
            else:
                total, goods_arr = query_goods_list(shop_id, keyword, sub_keyword,
                                                    class_id, fast_food_flag, keyword_type, page)
                if total == 0 and keyword_len == 13:
                    barcode_data = goods_barcode.get_barcode_from_archives(keyword)
                    if len(barcode_data.keys()):
                        resp_ext.update({'barcode_data': barcode_data})
            if db_shop_is_half > 0:
                shop_is_half = db_shop_is_half
                for goods_row in goods_arr:
                    goods_row.update({'is_half': db_shop_is_half})
    except Exception as e:
        print(e.args)
    return total, goods_arr, weight_str, shop_is_half, resp_ext


def get_goods_class_by_goods_ids(goods_arr):
    class_id_set = set()
    for goods_item in goods_arr:
        class_id = str(goods_item.get('class_id'))
        class_id_set.add(class_id)
    class_id_num = len(class_id_set)
    if class_id_num > 0:
        dh = DB('table_goods_class')
        where = 'id in ({})'.format(','.join(class_id_set))
        rows = dh.find(['id', 'class_name', 'risk_grade'], where)
        cls_map = {}
        for row in rows:
            cls_id = row.get('id')
            class_name = row.get('class_name')
            risk_grade = row.get('risk_grade')
            cls_map.update({cls_id: {
                'class_name': class_name,
                'risk_grade': risk_grade,
            }})
        for goods_item in goods_arr:
            cls_id = goods_item.get('class_id')
            cls_dat = cls_map.get(cls_id)
            goods_item.update(cls_dat)
    return goods_arr


def get_goods_class_by_id(class_id, fields=None):
    if fields is None:
        fields = ['risk_grade']
    dh = DB('table_goods_class')
    return dh.one(fields, {'id': class_id})


def get_goods_by_id(cursor, shop_id, goods_id, fields=None, my_filters=None):
    if fields is None:
        fields = GoodsDetailFields
    filters = {
        'id': goods_id,
        'shoper_id': shop_id,
    }
    if type(my_filters) is dict:
        filters.update(my_filters)
    dh = DB('table_goods', conn=cursor)
    return dh.one(fields, filters)


def count_shop_goods_num(cursor, shop_id):
    dh = DB('table_goods', conn=cursor)
    return dh.count('shoper_id=%s and is_delete=0', [shop_id])
